#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;

--导入dwd   todo（清洗转换去空去重）
-- 空值处理: coalesce函数  nvl函数  where is not null
	/*日期转换:  date timestamp 年月日字段的抽取
	枚举类型解释： 1 2 3 4   case when
	脱敏操作： 手机号 身份证号码  138****5558
		    分事实、维度，什么是事实、什么是维度--->这一扩展可能会扯到维度建模整个理论
	（day03）
	 事实：你分析关注的内容
	 维度：分析问题的角度
	 */
insert overwrite table ol_edu_dwd.dim_intent_appeal
select
        customer_relationship_first_id,
        case appeal_status when 0 then '待稽核'
                when 1 then '无效'
                when 2 then '有效'
                else 'other' end as appeal_status
from ol_edu_ods.customer_appeal;

insert overwrite table ol_edu_dwd.dim_intent_clue
select
        id as clue_id,
        customer_relationship_id,
        case when clue_state='VALID_PUBLIC_NEW_CLUE' or clue_state='VALID_NEW_CLUES' then '新学员'
                when clue_state='INVALID_PUBLIC_OLD_CLUE' then '老学员'
                else 'other' end as clue_state,
        itcast_school_id,
        itcast_school,
        itcast_subject_id,
        itcast_subject,
        origin_channel
from ol_edu_ods.customer_clue;

insert overwrite table ol_edu_dwd.dim_intent_customer
select
        id as customer_id,
        area
from ol_edu_ods.customer;

insert overwrite table ol_edu_dwd.dim_intent_department
select
        id as  department_id,
        name as department_name,
        parent_id
from ol_edu_ods.scrm_department;

insert overwrite table ol_edu_dwd.dim_intent_employee
select
        id as employee_id,
        tdepart_id
from ol_edu_ods.employee;

insert overwrite table ol_edu_dwd.fact_intent_relationship
select
        id as intent_id,
        create_date_time,
        update_date_time,
        deleted as intent_deleted,
        customer_id,
        case when origin_type='NETSERVICE' or origin_type='PHONE' then '线上'
                else '线下' end as origin_type,
        creator
from ol_edu_ods.customer_relationship;
"